﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DormMS.Common;
using DormMS.IService;
using DormMS.Model;
using DormMS.Model.SearchModels;
using DormMS.Model.ViewModels;
using Infrastructure.Dao;
using SqlSugar;


namespace DormMS.Service
{
    public class StudentBedService : IStudentBedService
    {
        public SqlSugarClient db;
        
        /// <summary>
        /// 通过床位Id和学生Id找到学生对应床位信息
        /// </summary>
        /// <param name="BedId"></param>
        /// <param name="StudentId"></param>
        /// <returns></returns>
        public StudentBed GetStudentBedByBedIdAndStudentId(int BedId, int StudentId)
        {
            using (db = DbConfig.GetInstance())
            { 

                var data = db.Queryable<StudentBed>().SingleOrDefault(x => x.BedId == BedId && x.StudentId == StudentId);

                return data;
            }
        }
        /// <summary>
        /// 通过Id获取学生对应床位信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public StudentBed GetStudentBedById(int Id)
        {
            using (db = DbConfig.GetInstance())
            {
                var data = db.Queryable<StudentBed>().SingleOrDefault(x => x.Id == Id);
                return data;
            }
        }

        /// <summary>
        /// 通过公寓Id查找床位对于学生信息
        /// </summary>
        /// <param name="RoomId"></param>
        /// <returns></returns>
        public List<V_StudentBed> GetStudentBedByRoomId(int RoomId)
        {
            db.CurrentFilterKey = "";
            using (db = DbConfig.GetInstance())
            {

                var data = from a in db.Queryable<StudentBed>().ToList()
                           join b in db.Queryable<Bed>().ToList() on a.BedId equals b.Id
                           join c in db.Queryable<Room>().ToList() on a.RoomId equals c.Id
                           join d in db.Queryable<Building>().ToList() on a.BuildingId equals d.Id
                           join e in db.Queryable<Class>().ToList() on a.ClassId equals e.Id
                           join f in db.Queryable<Department>().ToList() on e.DepartmentId equals f.Id
                           join g in db.Queryable<UserInfo>().ToList() on e.TeacherId equals g.Id
                           join h in db.Queryable<UserInfo>().ToList() on a.StudentId equals h.Id
                           where g.IsTeacher == 1 && a.IsDeleted == false
                           orderby d.BuildingNO, c.RoomNO, h.UserName
                           select new V_StudentBed
                           {
                               Id = a.Id,
                               BuildingId = d.Id,
                               BuildingNO = d.BuildingNO,
                               BedId = b.Id,
                               BedNO = b.BedNO,
                               RoomId = c.Id,
                               RoomNO = c.RoomNO,
                               DepartmentId = f.Id,
                               ClassId = e.Id,
                               ClassName = e.ClassName,
                               CupboardNO = b.CupboardNO,
                               DepartmentName = f.DepartmentName,
                               TeacherName = g.RealName,
                               StudentNO = h.UserName,
                               StudentSex = GetSysCodeByCodeNameAndCode("性别", g.Sex) == null ? "" : GetSysCodeByCodeNameAndCode("性别", g.Sex).CodeName,
                               StudentName = h.RealName,
                           };
                return data.Where(x => x.RoomId == RoomId).ToList();

            }
        }
        /// <summary>
        /// 通过查询获取学生对应床位列表
        /// </summary>
        /// <param name="search"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<V_StudentBed> GetStudentBedBySearch(StudentBedSearchModel search, ref int total)
        {
            using (db= DbConfig.GetInstance())
            {
                var data = from a in db.Queryable<StudentBed>().ToList()
                           join b in db.Queryable<Bed>().ToList() on a.BedId equals b.Id
                           join c in db.Queryable<Room>().ToList() on a.RoomId equals c.Id
                           join d in db.Queryable<Building>().ToList() on a.BuildingId equals d.Id
                           join e in db.Queryable<Class>().ToList() on a.ClassId equals e.Id
                           join f in db.Queryable<Department>().ToList() on e.DepartmentId equals f.Id
                           join g in db.Queryable<UserInfo>().ToList() on e.TeacherId equals g.Id
                           join h in db.Queryable<UserInfo>().ToList() on a.StudentId equals h.Id
                           where g.IsTeacher == 1 && a.IsDeleted == false
                           orderby d.BuildingNO, c.RoomNO, h.UserName
                           select new V_StudentBed
                           {
                               Id = a.Id,
                               BuildingId = d.Id,
                               BuildingNO = d.BuildingNO,
                               BedId = b.Id,
                               BedNO = b.BedNO,
                               RoomId = c.Id,
                               RoomNO = c.RoomNO,
                               DepartmentId = f.Id,
                               ClassId = e.Id,
                               ClassName = e.ClassName,
                               CupboardNO = b.CupboardNO,
                               DepartmentName = f.DepartmentName,
                               TeacherId = g.Id,
                               TeacherName = g.RealName,
                               StudentNO = h.UserName,
                               StudentSex = GetSysCodeByCodeNameAndCode("性别", g.Sex) == null ? "" : GetSysCodeByCodeNameAndCode("性别", g.Sex).CodeName,
                               StudentName = h.RealName,
                           }                           ;
                if (search != null)
                {
                    if (search.BuildingId != 0)
                    {
                        data = data.Where(x => x.BuildingId == search.BuildingId);
                    }
                    if (search.RoomId != 0)
                    {
                        data = data.Where(x => x.RoomId == search.RoomId);
                    }
                    if (search.DepartmentId != 0)
                    {
                        data = data.Where(x => x.DepartmentId == search.DepartmentId);
                    }
                    if (search.ClassId != 0)
                    {
                        data = data.Where(x => x.ClassId == search.ClassId);
                    }
                    if (search.TeacherId != 0)
                    {
                        data = data.Where(x => x.TeacherId == search.TeacherId);
                    }
                    if (string.IsNullOrEmpty(search.StudentNO) != true)
                    {
                        data = data.Where(x => x.BuildingId == search.BuildingId);
                    }

                    if (string.IsNullOrEmpty(search.StudentName) != true)
                    {
                        data = data.Where(x => x.BuildingId == search.BuildingId);
                    }
                }

                var list = data.Skip(search.Skip).Take(search.Take).ToList();
                total = data.Count();
                return list;
            }
            
        }
        /// <summary>
        /// 增加学生对应床位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int StudentBedAdd(StudentBed model)
        {
            using (db = DbConfig.GetInstance())
            {                
                return db.Insert<StudentBed>(model).ObjToInt();
            }
        }

        /// <summary>
        /// 删除学生对应床位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int StudentBedDel(StudentBed model)
        {
            using (db = DbConfig.GetInstance())
            {
                return db.Update<StudentBed>(model).ObjToInt();
            }
        }
        /// <summary>
        /// 编辑学生对应床位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int StudentBedEdit(StudentBed model, int? bedId)
        {
            using (db = DbConfig.GetInstance())
            {
                if (bedId != null)
                {
                    Bed bed = BaseService.LoadEntity<Bed>(b => b.Id == bedId);
                    bed.Status = 0;

                    db.Update<Bed>(bed);
                }

                return db.Update<StudentBed>(model).ObjToInt();
            }
        }
        /// <summary>
        /// 获取性别
        /// </summary>
        /// <param name="CodeType"></param>
        /// <param name="Code"></param>
        /// <returns></returns>
        private SysCode GetSysCodeByCodeNameAndCode(string CodeType, int Code)
        {
            using (db = DbConfig.GetInstance())
            {
                db.CurrentFilterKey = "";
                var data = db.Queryable<SysCode>().SingleOrDefault(x => x.CodeType == CodeType && x.Code == Code);
                return data;
            }
        }
    }
}
